﻿using Dapper.Contrib.Extensions;
using Dapper;
using EWC.HIS.Domain;
using System;
using System.Collections.Generic;
using System.Data;
using System.Threading.Tasks;
using SqlKata.Compilers;
using SqlKata;
using EWC.HIS.Domain.Data.Repositories;
using EWC.HIS.Domain.Data.Enums;

namespace EWC.HIS.Dapper.Data
{
    /// <summary>
    /// 实体仓储基类 
    /// </summary>
    /// <typeparam name="TEntity"></typeparam>
    public abstract class RepositoryBase<TEntity> where TEntity : BaseEntity
    {
        // 数据库上下文对象
        protected readonly IDbContext DbContext;

        // 构造函数注入数据库上下文对象
        protected RepositoryBase(IDbContext dbContext)
        {
            DbContext = dbContext;

            // 设置该仓储的数据库连接字符串
            DbContext.DbConnStr = DbConnStr;
        }

        // 数据库连接字符串属性，默认使用 HIS 数据库
        protected virtual EDbConnStr DbConnStr => EDbConnStr.HIS;

        /// <summary>
        /// 数据表名(默认类名，如果不是，需要在子类重写)
        /// </summary>
        protected virtual string TableName
        {
            get
            {
                // 获取当前泛型类型的 TableAttribute 标识
                TableAttribute tableAttribute = (TableAttribute)Attribute.GetCustomAttribute(typeof(TEntity), typeof(TableAttribute));

                if (tableAttribute != null)
                {
                    // 该实体类型有 TableAttribute 标识，返回标识的表名
                    return tableAttribute.Name;
                }

                // 否则，返回该实体类型的类名
                return typeof(TEntity).Name;
            }
        }

        /// <summary>
        /// 获取对象到 SQL 语句的查询结果对象
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        protected virtual SqlResult GetSqlResult(Query query)
        {
            // 创建一个新的 SQLServer 查询编译器实例
            var compiler = new SqlServerCompiler
            {
                // 设置使用新版分页
                UseLegacyPagination = false
            };

            // 使用编译器对象对给定的查询进行编译，得到编译后的 SQL 语句和参数列表
            SqlResult sqlResult = compiler.Compile(query);

            // 返回编译后的 SQL 查询结果对象
            return sqlResult;
        }

        /// <summary>
        /// 根据指定 ID 异步获取实体对象
        /// </summary>
        /// <param name="id"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual async Task<TEntity> GetAsync(int id, int? commandTimeout = null)
        {
            if (id == 0)
                return null;

            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 GetAsync 方法异步获取指定 ID 的实体对象
            var result = await _dbConnection.GetAsync<TEntity>(id, transaction, commandTimeout);

            return result;
        }

        /// <summary>
        /// 异步查询第一个匹配的实体对象
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual async Task<TEntity> GetFirstOrDefaultAsync(string sql, object param = null, int? commandTimeout = null)
        {
            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 QueryFirstOrDefaultAsync 方法异步查询第一个匹配的实体对象
            var result = await _dbConnection.QueryFirstOrDefaultAsync<TEntity>(sql, param, transaction, commandTimeout);

            return result;
        }

        /// <summary>
        /// 异步查询匹配条件的实体列表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public virtual async Task<IEnumerable<TEntity>> GetListAsync(string sql, object param = null,
            int? commandTimeout = null, CommandType? commandType = null)
        {
            if (string.IsNullOrEmpty(sql))
                return null;

            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 QueryAsync 方法异步查询匹配条件的实体列表
            var result = await _dbConnection.QueryAsync<TEntity>(sql, param, transaction, commandTimeout, commandType);

            return result;
        }

        /// <summary>
        /// 异步插入实体对象
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual async Task<int> InsertAsync(TEntity entity, int? commandTimeout = null)
        {
            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 InsertAsync 方法异步插入实体对象
            int result = await _dbConnection.InsertAsync(entity, transaction, commandTimeout);

            return result;
        }

        /// <summary>
        /// 异步更新实体对象
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual async Task<bool> UpdateAsync(TEntity entity, int? commandTimeout = null)
        {
            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 UpdateAsync 方法异步更新实体对象
            var result = await _dbConnection.UpdateAsync(entity, transaction, commandTimeout);

            return result;
        }

        /// <summary>
        /// 异步删除实体对象
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="commandTimeout"></param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(TEntity entity, int? commandTimeout = null)
        {
            var _dbConnection = await DbContext.GetConnectionAsync(); var transaction = DbContext.Transaction;
            // 通过 Dapper 的 DeleteAsync 方法异步删除实体对象
            var result = await _dbConnection.DeleteAsync(entity, transaction, commandTimeout);

            return result;
        }

        /// <summary>
        /// 异步执行 SQL 查找和更新命令
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public virtual async Task<int> ExecuteAsync(string sql, object param = null, int? commandTimeout = null,
            CommandType? commandType = null)
        {
            var _dbConnection = await DbContext.GetConnectionAsync();
            var transaction = DbContext.Transaction;

            // 通过 Dapper 的 ExecuteAsync 方法异步执行 SQL 查找和更新命令
            var result = await _dbConnection.ExecuteAsync(sql, param, transaction, commandTimeout, commandType);
            return result;
        }



        #region 根据反射生成sql语句
        ///// <summary>
        ///// 生成添加语句
        ///// </summary>
        ///// <returns></returns>
        //private string InsertSql()
        //{
        //    Type t = typeof(TEntity);
        //    StringBuilder p_sql = new StringBuilder();
        //    var ps = t.GetProperties();
        //    IList<string> field = new List<string>();
        //    IList<string> param = new List<string>();
        //    foreach (var p in ps)
        //    {
        //        //仅查询字段不在其内
        //        var only = p.GetCustomAttribute<OnlyQueryAttribute>();
        //        if (only != null)
        //        {
        //            continue;
        //        }
        //        string name = p.Name;
        //        var column = p.GetCustomAttribute<ColumnAttribute>();
        //        if (column != null)
        //        {
        //            name = column.Name;
        //        }
        //        if (name.ToUpper() == PrimaryKeyName.ToUpper() && IsAutoIncrement)
        //        {
        //            continue;
        //        }
        //        param.Add("@" + p.Name);
        //        field.Add(name);
        //    }
        //    p_sql.Append($"insert into {TableName}({string.Join(",", field)}) values({string.Join(",", param)});");
        //    return p_sql.ToString();
        //}
        ///// <summary>
        ///// 生成修改语句
        ///// </summary>
        ///// <returns></returns>
        //private string UpdateSql()
        //{
        //    Type t = typeof(TEntity);
        //    StringBuilder p_sql = new StringBuilder();
        //    var ps = t.GetProperties();
        //    string keyName = string.Empty;
        //    IList<string> field = new List<string>();
        //    foreach (var p in ps)
        //    {
        //        //仅查询字段不在修改
        //        var only = p.GetCustomAttribute<OnlyQueryAttribute>();
        //        if (only != null)
        //        {
        //            continue;
        //        }
        //        string name = p.Name;
        //        var column = p.GetCustomAttribute<ColumnAttribute>();
        //        if (column != null)
        //        {
        //            name = column.Name;
        //        }
        //        if (name.ToUpper() == PrimaryKeyName.ToUpper())
        //        {
        //            keyName = name;
        //            continue;
        //        }
        //        field.Add($"{name}=@{p.Name}");
        //    }
        //    p_sql.Append($"update {TableName} set {string.Join(", ", field)} where {PrimaryKeyName} = @{keyName};");
        //    return p_sql.ToString();
        //}
        #endregion
    }
}
